{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "b85cbfe6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Py3_Jupyter_Nb_讯捷集团_BI数据转换(区域-门店-节日)_GF_2024-01-04.ipynb\n",
    "# Create By GF 2024-01-04 17:38"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "0fde47f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "d541d58b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "# --------------------------------------------------\n",
    "plt.rcParams[\"font.sans-serif\"] = [\"SimHei\"]\n",
    "plt.rcParams[\"axes.unicode_minus\"] = False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "385dddb6",
   "metadata": {},
   "outputs": [],
   "source": [
    "def MapFunc_XunJie_Judgement_Continuous_Growth(X1:float, X2:float, X3:float):\n",
    "    \n",
    "    \"\"\"\n",
    "    [Require] import numpy as np\n",
    "    \n",
    "    [Explain] +---------+---------+---------+----------------+\n",
    "              |标签     |2021     |2022     |2023            |\n",
    "              +---------+---------+---------+----------------+\n",
    "              |连续增长 |X >= 0.0 |X >= 0.0 |X >= 0.1        |\n",
    "              |次增长   |X >= 0.0 |X >= 0.0 |0.0 <= X < 0.1  |\n",
    "              |次增长   |         |X >= 0.0 |X >= 0.2        |\n",
    "              |次增长   |         |         |X >= 0.3        |\n",
    "              |增长     |         |         |0.2 <= X < 0.3  |\n",
    "              |正常     |         |         |0.0 <= X < 0.2  |\n",
    "              |次降     |         |         |-0.1 <= X < 0.0 |\n",
    "              |下降     |         |         |X < -0.1        |\n",
    "              |新开     |         |         |23年新开        |\n",
    "              +---------+---------+---------+----------------+\n",
    "    \"\"\"\n",
    "    if (0.0    <= X1) and (0.0    <= X2) and (0.1    <= X3):               return str(\"连续增长\")\n",
    "    if (0.0    <= X1) and (0.0    <= X2) and (0.0    <= X3 and X3 < 0.1): return str(\"次增长\")\n",
    "    if (np.nan == X1) and (0.0    <= X2) and (0.2    <= X3):               return str(\"次增长\")\n",
    "    if (np.nan == X1) and (np.nan == X2) and (0.3    <= X3):               return str(\"次增长\")\n",
    "    if (np.nan == X1) and (np.nan == X2) and (0.2    <= X3 and X3 < 0.3): return str(\"增长\")\n",
    "    if (np.nan == X1) and (np.nan == X2) and (0.0    <= X3 and X3 < 0.2): return str(\"正常\")\n",
    "    if (np.nan == X1) and (np.nan == X2) and (-0.1   <= X3 and X3 < 0.0): return str(\"次降\")\n",
    "    if (np.nan == X1) and (np.nan == X2) and (-0.1   <= X3):               return str(\"下降\")\n",
    "    if (np.nan == X1) and (np.nan == X2) and (np.nan == X3):               return str(\"新开\")\n",
    "    \n",
    "    # ##############################################\n",
    "    # End of Function.\n",
    "\n",
    "def DtmFunc_Festival_Allocate_Serial_Number(FestivalName:str):\n",
    "\n",
    "    \"\"\"\n",
    "    [Example] FestivalName = \"元旦节\"\n",
    "              Result = DtmFunc_Festival_Allocate_Serial_Number(FestivalName)\n",
    "              \n",
    "              print(Result)\n",
    "              >>> '(1)元旦节'\n",
    "    \"\"\"\n",
    "    if (type(FestivalName) is float): return FestivalName\n",
    "    # ----------------------------------------------\n",
    "    if (\"元旦\" in FestivalName): return \"(1)%s\" % FestivalName\n",
    "    if (\"春节\" in FestivalName): return \"(2)%s\" % FestivalName\n",
    "    if (\"清明\" in FestivalName): return \"(3)%s\" % FestivalName\n",
    "    if (\"劳动\" in FestivalName): return \"(4)%s\" % FestivalName\n",
    "    if (\"端午\" in FestivalName): return \"(5)%s\" % FestivalName\n",
    "    # ..............................................\n",
    "    if (\"中秋\" in FestivalName) and (\"国庆\" in FestivalName): return \"(6)%s\" % FestivalName\n",
    "    if (\"中秋\" in FestivalName) and (\"国庆\" not in FestivalName): return \"(6)%s\" % FestivalName\n",
    "    if (\"国庆\" in FestivalName) and (\"中秋\" not in FestivalName): return \"(7)%s\" % FestivalName\n",
    "    # ..............................................\n",
    "    if (\"圣诞\" in FestivalName): return \"(8)%s\" % FestivalName\n",
    "    \n",
    "    # ##############################################\n",
    "    # End of Function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "b65ede30",
   "metadata": {},
   "outputs": [],
   "source": [
    "StoreProfitPDF = pd.read_csv(\"./Datas/CSV数据_讯捷集团_毛利数据_门店毛利数据_2020-01-01至2023-12-31_GB2312.csv\", encoding=\"gb2312\")\n",
    "# ..................................................\n",
    "CalendarPDF = pd.read_excel(\"./Datas/日历_2020-2024_节日及事件_GF_2024-01-04.xlsx\", sheet_name=\"Sheet1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "88b7cf94",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>月</th>\n",
       "      <th>周内第几天</th>\n",
       "      <th>企业体系</th>\n",
       "      <th>企业中心</th>\n",
       "      <th>门店等级</th>\n",
       "      <th>门店闭店日期</th>\n",
       "      <th>区域类别</th>\n",
       "      <th>毛利</th>\n",
       "      <th>毛利月合计</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-12-01</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>形象</td>\n",
       "      <td>三中心</td>\n",
       "      <td>C</td>\n",
       "      <td>NaN</td>\n",
       "      <td>外围</td>\n",
       "      <td>5463.482</td>\n",
       "      <td>93891.9210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-12-01</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>华为</td>\n",
       "      <td>三中心</td>\n",
       "      <td>C</td>\n",
       "      <td>NaN</td>\n",
       "      <td>二级</td>\n",
       "      <td>797.060</td>\n",
       "      <td>70332.8087</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-12-01</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>形象</td>\n",
       "      <td>三中心</td>\n",
       "      <td>A+1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>二级</td>\n",
       "      <td>23408.760</td>\n",
       "      <td>530026.7020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-12-01</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>形象</td>\n",
       "      <td>二中心</td>\n",
       "      <td>A1预</td>\n",
       "      <td>NaN</td>\n",
       "      <td>郊县</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-12-01</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>形象</td>\n",
       "      <td>三中心</td>\n",
       "      <td>A1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>郊县</td>\n",
       "      <td>7217.090</td>\n",
       "      <td>306572.8570</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343433</th>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>形象</td>\n",
       "      <td>二中心</td>\n",
       "      <td>B2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>郊县</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343434</th>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>形象</td>\n",
       "      <td>四中心</td>\n",
       "      <td>B1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>市区</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343435</th>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>形象</td>\n",
       "      <td>二中心</td>\n",
       "      <td>A1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>郊县</td>\n",
       "      <td>2372.950</td>\n",
       "      <td>317853.2867</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343436</th>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>形象</td>\n",
       "      <td>二中心</td>\n",
       "      <td>A1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>外围</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343437</th>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>形象</td>\n",
       "      <td>四中心</td>\n",
       "      <td>B2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>郊县</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>343438 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                日期   月  周内第几天 企业体系 企业中心 门店等级 门店闭店日期 区域类别         毛利  \\\n",
       "0       2023-12-01  12      6   形象  三中心    C    NaN   外围   5463.482   \n",
       "1       2023-12-01  12      6   华为  三中心    C    NaN   二级    797.060   \n",
       "2       2023-12-01  12      6   形象  三中心  A+1    NaN   二级  23408.760   \n",
       "3       2023-12-01  12      6   形象  二中心  A1预    NaN   郊县        NaN   \n",
       "4       2023-12-01  12      6   形象  三中心   A1    NaN   郊县   7217.090   \n",
       "...            ...  ..    ...  ...  ...  ...    ...  ...        ...   \n",
       "343433  2020-01-31   1      6   形象  二中心   B2    NaN   郊县        NaN   \n",
       "343434  2020-01-31   1      6   形象  四中心   B1    NaN   市区        NaN   \n",
       "343435  2020-01-31   1      6   形象  二中心   A1    NaN   郊县   2372.950   \n",
       "343436  2020-01-31   1      6   形象  二中心   A1    NaN   外围        NaN   \n",
       "343437  2020-01-31   1      6   形象  四中心   B2    NaN   郊县        NaN   \n",
       "\n",
       "              毛利月合计  \n",
       "0        93891.9210  \n",
       "1        70332.8087  \n",
       "2       530026.7020  \n",
       "3               NaN  \n",
       "4       306572.8570  \n",
       "...             ...  \n",
       "343433          NaN  \n",
       "343434          NaN  \n",
       "343435  317853.2867  \n",
       "343436          NaN  \n",
       "343437          NaN  \n",
       "\n",
       "[343438 rows x 10 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "StoreProfitPDF[StoreProfitPDF.columns[0::3]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "efc319cf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>月</th>\n",
       "      <th>周内第几天</th>\n",
       "      <th>企业体系</th>\n",
       "      <th>企业中心</th>\n",
       "      <th>门店等级</th>\n",
       "      <th>门店闭店日期</th>\n",
       "      <th>区域类别</th>\n",
       "      <th>毛利</th>\n",
       "      <th>毛利月合计</th>\n",
       "      <th>统计年份</th>\n",
       "      <th>标记</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-12-01</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>形象</td>\n",
       "      <td>三中心</td>\n",
       "      <td>C</td>\n",
       "      <td>NaT</td>\n",
       "      <td>外围</td>\n",
       "      <td>5463.482</td>\n",
       "      <td>93891.9210</td>\n",
       "      <td>2023</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-12-01</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>华为</td>\n",
       "      <td>三中心</td>\n",
       "      <td>C</td>\n",
       "      <td>NaT</td>\n",
       "      <td>二级</td>\n",
       "      <td>797.060</td>\n",
       "      <td>70332.8087</td>\n",
       "      <td>2023</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-12-01</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>形象</td>\n",
       "      <td>三中心</td>\n",
       "      <td>A+1</td>\n",
       "      <td>NaT</td>\n",
       "      <td>二级</td>\n",
       "      <td>23408.760</td>\n",
       "      <td>530026.7020</td>\n",
       "      <td>2023</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-12-01</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>形象</td>\n",
       "      <td>二中心</td>\n",
       "      <td>A1预</td>\n",
       "      <td>NaT</td>\n",
       "      <td>郊县</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023</td>\n",
       "      <td>Invalid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-12-01</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>形象</td>\n",
       "      <td>三中心</td>\n",
       "      <td>A1</td>\n",
       "      <td>NaT</td>\n",
       "      <td>郊县</td>\n",
       "      <td>7217.090</td>\n",
       "      <td>306572.8570</td>\n",
       "      <td>2023</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343433</th>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>形象</td>\n",
       "      <td>二中心</td>\n",
       "      <td>B2</td>\n",
       "      <td>NaT</td>\n",
       "      <td>郊县</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020</td>\n",
       "      <td>Invalid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343434</th>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>形象</td>\n",
       "      <td>四中心</td>\n",
       "      <td>B1</td>\n",
       "      <td>NaT</td>\n",
       "      <td>市区</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020</td>\n",
       "      <td>Invalid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343435</th>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>形象</td>\n",
       "      <td>二中心</td>\n",
       "      <td>A1</td>\n",
       "      <td>NaT</td>\n",
       "      <td>郊县</td>\n",
       "      <td>2372.950</td>\n",
       "      <td>317853.2867</td>\n",
       "      <td>2020</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343436</th>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>形象</td>\n",
       "      <td>二中心</td>\n",
       "      <td>A1</td>\n",
       "      <td>NaT</td>\n",
       "      <td>外围</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343437</th>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>形象</td>\n",
       "      <td>四中心</td>\n",
       "      <td>B2</td>\n",
       "      <td>NaT</td>\n",
       "      <td>郊县</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020</td>\n",
       "      <td>Invalid</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>343438 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               日期   月  周内第几天 企业体系 企业中心 门店等级 门店闭店日期 区域类别         毛利  \\\n",
       "0      2023-12-01  12      6   形象  三中心    C    NaT   外围   5463.482   \n",
       "1      2023-12-01  12      6   华为  三中心    C    NaT   二级    797.060   \n",
       "2      2023-12-01  12      6   形象  三中心  A+1    NaT   二级  23408.760   \n",
       "3      2023-12-01  12      6   形象  二中心  A1预    NaT   郊县        NaN   \n",
       "4      2023-12-01  12      6   形象  三中心   A1    NaT   郊县   7217.090   \n",
       "...           ...  ..    ...  ...  ...  ...    ...  ...        ...   \n",
       "343433 2020-01-31   1      6   形象  二中心   B2    NaT   郊县        NaN   \n",
       "343434 2020-01-31   1      6   形象  四中心   B1    NaT   市区        NaN   \n",
       "343435 2020-01-31   1      6   形象  二中心   A1    NaT   郊县   2372.950   \n",
       "343436 2020-01-31   1      6   形象  二中心   A1    NaT   外围        NaN   \n",
       "343437 2020-01-31   1      6   形象  四中心   B2    NaT   郊县        NaN   \n",
       "\n",
       "              毛利月合计  统计年份       标记  \n",
       "0        93891.9210  2023     None  \n",
       "1        70332.8087  2023     None  \n",
       "2       530026.7020  2023     None  \n",
       "3               NaN  2023  Invalid  \n",
       "4       306572.8570  2023     None  \n",
       "...             ...   ...      ...  \n",
       "343433          NaN  2020  Invalid  \n",
       "343434          NaN  2020  Invalid  \n",
       "343435  317853.2867  2020     None  \n",
       "343436          NaN  2020     None  \n",
       "343437          NaN  2020  Invalid  \n",
       "\n",
       "[343438 rows x 12 columns]"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 数据计算: 统计信息整理。\n",
    "# --------------------------------------------------\n",
    "Stat_StoreProfitPDF = StoreProfitPDF.copy()\n",
    "# ..................................................\n",
    "Stat_StoreDepartRegionPDF = StoreProfitPDF[[\"门店名称\", \"企业部门\", \"企业体系\", \"区域类别\", \"区域商圈\"]]\n",
    "# ..................................................\n",
    "Stat_StoreLevelPDF = StoreProfitPDF[[\"门店名称\", \"门店等级\"]]\n",
    "# ..................................................\n",
    "Stat_CalendarFestivalPDF = CalendarPDF[[\"日期\", \"节日所属年份\", \"节日标注\", \"节日名称\"]]\n",
    "\n",
    "# 类型转换。\n",
    "# ..................................................\n",
    "Stat_StoreProfitPDF[\"日期\"] = Stat_StoreProfitPDF[\"日期\"].astype(\"datetime64[ns]\")\n",
    "Stat_StoreProfitPDF[\"门店开店日期\"] = Stat_StoreProfitPDF[\"门店开店日期\"].astype(\"datetime64[ns]\")\n",
    "Stat_StoreProfitPDF[\"门店闭店日期\"] = Stat_StoreProfitPDF[\"门店闭店日期\"].astype(\"datetime64[ns]\")\n",
    "\n",
    "# 数据提取。\n",
    "# ..................................................\n",
    "Stat_StoreProfitPDF[\"统计日期\"] = Stat_StoreProfitPDF[\"日期\"]\n",
    "Stat_StoreProfitPDF[\"统计年份\"] = Stat_StoreProfitPDF[\"日期\"].dt.year\n",
    "Stat_StoreProfitPDF[\"统计月份\"] = Stat_StoreProfitPDF[\"日期\"].dt.month\n",
    "Stat_StoreProfitPDF[\"统计月日\"] = Stat_StoreProfitPDF[\"日期\"].dt.strftime(\"%m-%d\")\n",
    "\n",
    "# 数据计算: 标记无效数据。\n",
    "# --------------------------------------------------\n",
    "Stat_StoreProfitPDF[\"标记\"] = None\n",
    "# ..................................................\n",
    "IdxArr = Stat_StoreProfitPDF[Stat_StoreProfitPDF[\"统计日期\"] < Stat_StoreProfitPDF[\"门店开店日期\"]].index\n",
    "# ..................................................\n",
    "Stat_StoreProfitPDF.loc[IdxArr, \"标记\"] = str(\"Invalid\")\n",
    "\n",
    "# 数据计算: 修正\"关店年份\"所在的\"统计年份\" (例: 2022年不可能统计到2023的关店信息)。\n",
    "# --------------------------------------------------\n",
    "IdxArr = Stat_StoreProfitPDF[Stat_StoreProfitPDF[\"统计日期\"] < Stat_StoreProfitPDF[\"门店闭店日期\"]].index\n",
    "# ..................................................\n",
    "Stat_StoreProfitPDF.loc[IdxArr, \"门店闭店日期\"] = pd.NaT\n",
    "\n",
    "# 数据计算。\n",
    "TMPPDF = Stat_CalendarFestivalPDF.copy()\n",
    "# ..................................................\n",
    "TMPPDF[\"节日名称\"] = TMPPDF[\"节日名称\"].apply(lambda x: DtmFunc_Festival_Allocate_Serial_Number(x))\n",
    "# ..................................................\n",
    "Stat_CalendarFestivalPDF = TMPPDF\n",
    "\n",
    "# 数据去重。\n",
    "# ..................................................\n",
    "Stat_StoreDepartRegionPDF = Stat_StoreDepartRegionPDF.drop_duplicates()\n",
    "Stat_StoreLevelPDF = Stat_StoreLevelPDF.drop_duplicates()\n",
    "\n",
    "# ##################################################\n",
    "Stat_StoreProfitPDF[Stat_StoreProfitPDF.columns[0::3]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "43df2d27",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>节日所属年份</th>\n",
       "      <th>节日名称</th>\n",
       "      <th>门店名称</th>\n",
       "      <th>节日起始日期</th>\n",
       "      <th>节日平均毛利</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1953</th>\n",
       "      <td>2021.0</td>\n",
       "      <td>(6)中秋节</td>\n",
       "      <td>云南丽江形象店</td>\n",
       "      <td>2021-09-19</td>\n",
       "      <td>7407.206667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2136</th>\n",
       "      <td>2021.0</td>\n",
       "      <td>(7)国庆节</td>\n",
       "      <td>云南丽江形象店</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>8566.864286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2320</th>\n",
       "      <td>2021.0</td>\n",
       "      <td>(8)圣诞节</td>\n",
       "      <td>云南丽江形象店</td>\n",
       "      <td>2021-12-24</td>\n",
       "      <td>5680.360000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2506</th>\n",
       "      <td>2022.0</td>\n",
       "      <td>(1)元旦</td>\n",
       "      <td>云南丽江形象店</td>\n",
       "      <td>2022-01-01</td>\n",
       "      <td>4749.783333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2693</th>\n",
       "      <td>2022.0</td>\n",
       "      <td>(2)春节</td>\n",
       "      <td>云南丽江形象店</td>\n",
       "      <td>2022-01-31</td>\n",
       "      <td>5189.652571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4864</th>\n",
       "      <td>2023.0</td>\n",
       "      <td>(4)劳动节</td>\n",
       "      <td>陕西西安高新万达形象店</td>\n",
       "      <td>2023-04-29</td>\n",
       "      <td>34804.520800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5075</th>\n",
       "      <td>2023.0</td>\n",
       "      <td>(5)端午节</td>\n",
       "      <td>陕西西安高新万达形象店</td>\n",
       "      <td>2023-06-22</td>\n",
       "      <td>27501.582333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5301</th>\n",
       "      <td>2023.0</td>\n",
       "      <td>(6)中秋节/国庆节</td>\n",
       "      <td>陕西西安高新万达形象店</td>\n",
       "      <td>2023-09-29</td>\n",
       "      <td>51685.191000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5528</th>\n",
       "      <td>2023.0</td>\n",
       "      <td>(8)圣诞节</td>\n",
       "      <td>陕西西安高新万达形象店</td>\n",
       "      <td>2023-12-24</td>\n",
       "      <td>26401.395000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5756</th>\n",
       "      <td>2024.0</td>\n",
       "      <td>(1)元旦</td>\n",
       "      <td>陕西西安高新万达形象店</td>\n",
       "      <td>2023-12-30</td>\n",
       "      <td>44557.662000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5757 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      节日所属年份        节日名称         门店名称     节日起始日期        节日平均毛利\n",
       "1953  2021.0      (6)中秋节      云南丽江形象店 2021-09-19   7407.206667\n",
       "2136  2021.0      (7)国庆节      云南丽江形象店 2021-10-01   8566.864286\n",
       "2320  2021.0      (8)圣诞节      云南丽江形象店 2021-12-24   5680.360000\n",
       "2506  2022.0       (1)元旦      云南丽江形象店 2022-01-01   4749.783333\n",
       "2693  2022.0       (2)春节      云南丽江形象店 2022-01-31   5189.652571\n",
       "...      ...         ...          ...        ...           ...\n",
       "4864  2023.0      (4)劳动节  陕西西安高新万达形象店 2023-04-29  34804.520800\n",
       "5075  2023.0      (5)端午节  陕西西安高新万达形象店 2023-06-22  27501.582333\n",
       "5301  2023.0  (6)中秋节/国庆节  陕西西安高新万达形象店 2023-09-29  51685.191000\n",
       "5528  2023.0      (8)圣诞节  陕西西安高新万达形象店 2023-12-24  26401.395000\n",
       "5756  2024.0       (1)元旦  陕西西安高新万达形象店 2023-12-30  44557.662000\n",
       "\n",
       "[5757 rows x 5 columns]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 数据计算: 节日平均毛利。\n",
    "# --------------------------------------------------\n",
    "SourceDataPDF = Stat_StoreProfitPDF.copy() # -> 拉取毛利所需的数据源。\n",
    "\n",
    "# 筛选标记。\n",
    "# ..................................................\n",
    "SourceDataPDF = SourceDataPDF[SourceDataPDF[\"标记\"] != \"Invalid\"]\n",
    "SourceDataPDF = SourceDataPDF[SourceDataPDF[\"统计月日\"] != \"02-29\"]\n",
    "SourceDataPDF = SourceDataPDF[[\"日期\", \"门店名称\", \"毛利\"]]\n",
    "\n",
    "# 数据连接。\n",
    "# ..................................................\n",
    "FestivalProfitAvgPDF = pd.merge(left=SourceDataPDF, right=Stat_CalendarFestivalPDF, how=\"left\", on=\"日期\")\n",
    "\n",
    "# 数据聚合。\n",
    "# ..................................................\n",
    "Grouped = FestivalProfitAvgPDF.groupby([\"节日所属年份\", \"节日名称\", \"门店名称\"], as_index=False)\n",
    "# ..................................................\n",
    "FestivalProfitAvgPDF = Grouped.agg({\"日期\":\"min\", \"毛利\":\"mean\"})\n",
    "# ..................................................\n",
    "FestivalProfitAvgPDF = FestivalProfitAvgPDF.rename(columns={\"日期\":\"节日起始日期\", \"毛利\":\"节日平均毛利\"})\n",
    "\n",
    "# ##################################################\n",
    "FestivalProfitAvgPDF.sort_values([\"门店名称\", \"节日起始日期\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "bf1cb4cd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>门店名称</th>\n",
       "      <th>毛利</th>\n",
       "      <th>前1日日期</th>\n",
       "      <th>前1日毛利</th>\n",
       "      <th>节日名称</th>\n",
       "      <th>节日平均毛利</th>\n",
       "      <th>前3日平均毛利</th>\n",
       "      <th>节日毛利增长率(对比前1日)</th>\n",
       "      <th>节日毛利增长率(对比前3日平均)</th>\n",
       "      <th>企业部门</th>\n",
       "      <th>企业体系</th>\n",
       "      <th>区域类别</th>\n",
       "      <th>区域商圈</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3662</th>\n",
       "      <td>2021-09-19</td>\n",
       "      <td>云南丽江形象店</td>\n",
       "      <td>7700.800</td>\n",
       "      <td>2021-09-18</td>\n",
       "      <td>5360.600</td>\n",
       "      <td>(6)中秋节</td>\n",
       "      <td>7407.206667</td>\n",
       "      <td>4894.4260</td>\n",
       "      <td>0.3818</td>\n",
       "      <td>0.5134</td>\n",
       "      <td>云南</td>\n",
       "      <td>形象</td>\n",
       "      <td>二级</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3605</th>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>云南丽江形象店</td>\n",
       "      <td>4299.378</td>\n",
       "      <td>2021-09-30</td>\n",
       "      <td>2492.438</td>\n",
       "      <td>(7)国庆节</td>\n",
       "      <td>8566.864286</td>\n",
       "      <td>6303.7127</td>\n",
       "      <td>2.4371</td>\n",
       "      <td>0.3590</td>\n",
       "      <td>云南</td>\n",
       "      <td>形象</td>\n",
       "      <td>二级</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3356</th>\n",
       "      <td>2021-12-24</td>\n",
       "      <td>云南丽江形象店</td>\n",
       "      <td>3567.190</td>\n",
       "      <td>2021-12-23</td>\n",
       "      <td>7364.118</td>\n",
       "      <td>(8)圣诞节</td>\n",
       "      <td>5680.360000</td>\n",
       "      <td>5298.6427</td>\n",
       "      <td>-0.2286</td>\n",
       "      <td>0.0720</td>\n",
       "      <td>云南</td>\n",
       "      <td>形象</td>\n",
       "      <td>二级</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3002</th>\n",
       "      <td>2022-01-01</td>\n",
       "      <td>云南丽江形象店</td>\n",
       "      <td>3755.970</td>\n",
       "      <td>2021-12-31</td>\n",
       "      <td>1925.032</td>\n",
       "      <td>(1)元旦</td>\n",
       "      <td>4749.783333</td>\n",
       "      <td>1830.1507</td>\n",
       "      <td>1.4674</td>\n",
       "      <td>1.5953</td>\n",
       "      <td>云南</td>\n",
       "      <td>形象</td>\n",
       "      <td>二级</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3132</th>\n",
       "      <td>2022-01-31</td>\n",
       "      <td>云南丽江形象店</td>\n",
       "      <td>704.530</td>\n",
       "      <td>2022-01-30</td>\n",
       "      <td>5514.090</td>\n",
       "      <td>(2)春节</td>\n",
       "      <td>5189.652571</td>\n",
       "      <td>7221.3093</td>\n",
       "      <td>-0.0588</td>\n",
       "      <td>-0.2813</td>\n",
       "      <td>云南</td>\n",
       "      <td>形象</td>\n",
       "      <td>二级</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1269</th>\n",
       "      <td>2023-04-29</td>\n",
       "      <td>陕西西安高新万达形象店</td>\n",
       "      <td>48768.815</td>\n",
       "      <td>2023-04-28</td>\n",
       "      <td>15933.250</td>\n",
       "      <td>(4)劳动节</td>\n",
       "      <td>34804.520800</td>\n",
       "      <td>13492.1500</td>\n",
       "      <td>1.1844</td>\n",
       "      <td>1.5796</td>\n",
       "      <td>西安</td>\n",
       "      <td>形象</td>\n",
       "      <td>外围</td>\n",
       "      <td>综合体内商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>865</th>\n",
       "      <td>2023-06-22</td>\n",
       "      <td>陕西西安高新万达形象店</td>\n",
       "      <td>9096.457</td>\n",
       "      <td>2023-06-21</td>\n",
       "      <td>18626.024</td>\n",
       "      <td>(5)端午节</td>\n",
       "      <td>27501.582333</td>\n",
       "      <td>31389.5483</td>\n",
       "      <td>0.4765</td>\n",
       "      <td>-0.1239</td>\n",
       "      <td>西安</td>\n",
       "      <td>形象</td>\n",
       "      <td>外围</td>\n",
       "      <td>综合体内商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>482</th>\n",
       "      <td>2023-09-29</td>\n",
       "      <td>陕西西安高新万达形象店</td>\n",
       "      <td>75722.491</td>\n",
       "      <td>2023-09-28</td>\n",
       "      <td>33613.486</td>\n",
       "      <td>(6)中秋节/国庆节</td>\n",
       "      <td>51685.191000</td>\n",
       "      <td>25734.4510</td>\n",
       "      <td>0.5376</td>\n",
       "      <td>1.0084</td>\n",
       "      <td>西安</td>\n",
       "      <td>形象</td>\n",
       "      <td>外围</td>\n",
       "      <td>综合体内商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>2023-12-24</td>\n",
       "      <td>陕西西安高新万达形象店</td>\n",
       "      <td>48278.284</td>\n",
       "      <td>2023-12-23</td>\n",
       "      <td>39768.889</td>\n",
       "      <td>(8)圣诞节</td>\n",
       "      <td>26401.395000</td>\n",
       "      <td>29352.7447</td>\n",
       "      <td>-0.3361</td>\n",
       "      <td>-0.1005</td>\n",
       "      <td>西安</td>\n",
       "      <td>形象</td>\n",
       "      <td>外围</td>\n",
       "      <td>综合体内商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>258</th>\n",
       "      <td>2023-12-30</td>\n",
       "      <td>陕西西安高新万达形象店</td>\n",
       "      <td>48368.741</td>\n",
       "      <td>2023-12-29</td>\n",
       "      <td>24274.915</td>\n",
       "      <td>(1)元旦</td>\n",
       "      <td>44557.662000</td>\n",
       "      <td>21601.2713</td>\n",
       "      <td>0.8355</td>\n",
       "      <td>1.0627</td>\n",
       "      <td>西安</td>\n",
       "      <td>形象</td>\n",
       "      <td>外围</td>\n",
       "      <td>综合体内商圈</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5757 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             日期         门店名称         毛利      前1日日期      前1日毛利        节日名称  \\\n",
       "3662 2021-09-19      云南丽江形象店   7700.800 2021-09-18   5360.600      (6)中秋节   \n",
       "3605 2021-10-01      云南丽江形象店   4299.378 2021-09-30   2492.438      (7)国庆节   \n",
       "3356 2021-12-24      云南丽江形象店   3567.190 2021-12-23   7364.118      (8)圣诞节   \n",
       "3002 2022-01-01      云南丽江形象店   3755.970 2021-12-31   1925.032       (1)元旦   \n",
       "3132 2022-01-31      云南丽江形象店    704.530 2022-01-30   5514.090       (2)春节   \n",
       "...         ...          ...        ...        ...        ...         ...   \n",
       "1269 2023-04-29  陕西西安高新万达形象店  48768.815 2023-04-28  15933.250      (4)劳动节   \n",
       "865  2023-06-22  陕西西安高新万达形象店   9096.457 2023-06-21  18626.024      (5)端午节   \n",
       "482  2023-09-29  陕西西安高新万达形象店  75722.491 2023-09-28  33613.486  (6)中秋节/国庆节   \n",
       "53   2023-12-24  陕西西安高新万达形象店  48278.284 2023-12-23  39768.889      (8)圣诞节   \n",
       "258  2023-12-30  陕西西安高新万达形象店  48368.741 2023-12-29  24274.915       (1)元旦   \n",
       "\n",
       "            节日平均毛利     前3日平均毛利  节日毛利增长率(对比前1日)  节日毛利增长率(对比前3日平均) 企业部门 企业体系  \\\n",
       "3662   7407.206667   4894.4260          0.3818            0.5134   云南   形象   \n",
       "3605   8566.864286   6303.7127          2.4371            0.3590   云南   形象   \n",
       "3356   5680.360000   5298.6427         -0.2286            0.0720   云南   形象   \n",
       "3002   4749.783333   1830.1507          1.4674            1.5953   云南   形象   \n",
       "3132   5189.652571   7221.3093         -0.0588           -0.2813   云南   形象   \n",
       "...            ...         ...             ...               ...  ...  ...   \n",
       "1269  34804.520800  13492.1500          1.1844            1.5796   西安   形象   \n",
       "865   27501.582333  31389.5483          0.4765           -0.1239   西安   形象   \n",
       "482   51685.191000  25734.4510          0.5376            1.0084   西安   形象   \n",
       "53    26401.395000  29352.7447         -0.3361           -0.1005   西安   形象   \n",
       "258   44557.662000  21601.2713          0.8355            1.0627   西安   形象   \n",
       "\n",
       "     区域类别     区域商圈  \n",
       "3662   二级  街铺-复合商圈  \n",
       "3605   二级  街铺-复合商圈  \n",
       "3356   二级  街铺-复合商圈  \n",
       "3002   二级  街铺-复合商圈  \n",
       "3132   二级  街铺-复合商圈  \n",
       "...   ...      ...  \n",
       "1269   外围   综合体内商圈  \n",
       "865    外围   综合体内商圈  \n",
       "482    外围   综合体内商圈  \n",
       "53     外围   综合体内商圈  \n",
       "258    外围   综合体内商圈  \n",
       "\n",
       "[5757 rows x 14 columns]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 数据计算: 节日毛利增长率。\n",
    "# --------------------------------------------------\n",
    "SourceDataPDF = Stat_StoreProfitPDF.copy() # -> 拉取毛利所需的数据源。\n",
    "\n",
    "# 筛选标记。\n",
    "# ..................................................\n",
    "SourceDataPDF = SourceDataPDF[SourceDataPDF[\"标记\"] != \"Invalid\"]\n",
    "SourceDataPDF = SourceDataPDF[SourceDataPDF[\"统计月日\"] != \"02-29\"]\n",
    "SourceDataPDF = SourceDataPDF[[\"日期\", \"门店名称\", \"毛利\"]]\n",
    "\n",
    "# 数据计算。\n",
    "# ..................................................\n",
    "SourceDataPDF[\"前1日日期\"] = SourceDataPDF[\"日期\"] - datetime.timedelta(days=1)\n",
    "\n",
    "# 数据连接: 自连接筛选前1日毛利。\n",
    "# ..................................................\n",
    "LEFTPDF = SourceDataPDF\n",
    "RIGHPDF = SourceDataPDF[[\"日期\", \"门店名称\", \"毛利\"]].rename(columns={\"日期\":\"前1日日期\", \"毛利\":\"前1日毛利\"})\n",
    "# ..................................................\n",
    "TMPPDF = pd.merge(left=LEFTPDF, right=RIGHPDF, how=\"left\", on=[\"门店名称\", \"前1日日期\"])\n",
    "# ..................................................\n",
    "FestivalProfitGrowthPDF = TMPPDF\n",
    "\n",
    "# 数据连接: 外连接对应节日平均毛利。\n",
    "# ..................................................\n",
    "LEFTPDF = TMPPDF\n",
    "RIGHPDF = FestivalProfitAvgPDF[[\"节日名称\", \"节日起始日期\", \"门店名称\", \"节日平均毛利\"]].rename(columns={\"节日起始日期\":\"日期\"})\n",
    "# ..................................................\n",
    "TMPPDF = pd.merge(left=LEFTPDF, right=RIGHPDF, how=\"left\", on=[\"门店名称\", \"日期\"])\n",
    "# ..................................................\n",
    "FestivalProfitGrowthPDF = TMPPDF\n",
    "\n",
    "# 数据分组计算。\n",
    "# ..................................................\n",
    "Grouped = TMPPDF.sort_values([\"门店名称\", \"日期\"]).groupby(\"门店名称\", as_index=False)\n",
    "# ..................................................\n",
    "Rolled = Grouped[\"毛利\"].shift(1).rolling(window=3, min_periods=1).mean()\n",
    "# ..................................................\n",
    "TMPPDF[\"前3日平均毛利\"] = round(Rolled[\"毛利\"], 4)\n",
    "# ..................................................\n",
    "FestivalProfitGrowthPDF = TMPPDF\n",
    "\n",
    "# 数据计算: 数据连接后计算。\n",
    "# ..................................................\n",
    "TMPPDF[\"节日毛利增长率(对比前1日)\"] = round((TMPPDF[\"节日平均毛利\"] - TMPPDF[\"前1日毛利\"]) / TMPPDF[\"前1日毛利\"], 4)\n",
    "# ..................................................\n",
    "TMPPDF[\"节日毛利增长率(对比前3日平均)\"] = round((TMPPDF[\"节日平均毛利\"] - TMPPDF[\"前3日平均毛利\"]) / TMPPDF[\"前3日平均毛利\"], 4)\n",
    "# ..................................................\n",
    "FestivalProfitGrowthPDF = TMPPDF\n",
    "\n",
    "# 筛选数据。\n",
    "# ..................................................\n",
    "TMPPDF = TMPPDF[TMPPDF[\"节日名称\"].isna() == False]\n",
    "# ..................................................\n",
    "FestivalProfitGrowthPDF = TMPPDF\n",
    "\n",
    "# 数据连接: 外连接对应部门和区域。\n",
    "# ..................................................\n",
    "LEFTPDF = TMPPDF\n",
    "RIGHPDF = Stat_StoreDepartRegionPDF\n",
    "# ..................................................\n",
    "TMPPDF = pd.merge(left=LEFTPDF, right=RIGHPDF, how=\"left\", on=\"门店名称\")\n",
    "# ..................................................\n",
    "FestivalProfitGrowthPDF = TMPPDF\n",
    "\n",
    "# 保存数据。\n",
    "# ..................................................\n",
    "FestivalProfitGrowthPDF.to_csv(\"./Datas/BI数据_讯捷集团_数据分析(节日毛利增长率).csv\", encoding=\"utf-8\", index=False)\n",
    "\n",
    "# ##################################################\n",
    "FestivalProfitGrowthPDF.sort_values([\"门店名称\", \"日期\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "900df747",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>门店名称</th>\n",
       "      <th>毛利</th>\n",
       "      <th>AVG60</th>\n",
       "      <th>去年同期(日期)</th>\n",
       "      <th>前年同期(日期)</th>\n",
       "      <th>去年同期(AVG60)</th>\n",
       "      <th>前年同期(AVG60)</th>\n",
       "      <th>企业部门</th>\n",
       "      <th>企业体系</th>\n",
       "      <th>区域类别</th>\n",
       "      <th>区域商圈</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-09-03</td>\n",
       "      <td>云南丽江形象店</td>\n",
       "      <td>4418.792</td>\n",
       "      <td>4418.7920</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>云南</td>\n",
       "      <td>形象</td>\n",
       "      <td>二级</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-09-04</td>\n",
       "      <td>云南丽江形象店</td>\n",
       "      <td>2149.805</td>\n",
       "      <td>3284.2985</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>云南</td>\n",
       "      <td>形象</td>\n",
       "      <td>二级</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-09-05</td>\n",
       "      <td>云南丽江形象店</td>\n",
       "      <td>3684.275</td>\n",
       "      <td>3417.6240</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>云南</td>\n",
       "      <td>形象</td>\n",
       "      <td>二级</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-09-06</td>\n",
       "      <td>云南丽江形象店</td>\n",
       "      <td>1356.160</td>\n",
       "      <td>2902.2580</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>云南</td>\n",
       "      <td>形象</td>\n",
       "      <td>二级</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-09-07</td>\n",
       "      <td>云南丽江形象店</td>\n",
       "      <td>1119.390</td>\n",
       "      <td>2545.6844</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>云南</td>\n",
       "      <td>形象</td>\n",
       "      <td>二级</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>270569</th>\n",
       "      <td>2023-12-27</td>\n",
       "      <td>陕西西安高新万达形象店</td>\n",
       "      <td>9389.107</td>\n",
       "      <td>23000.2164</td>\n",
       "      <td>2022-12-27</td>\n",
       "      <td>NaT</td>\n",
       "      <td>14431.6945</td>\n",
       "      <td>NaN</td>\n",
       "      <td>西安</td>\n",
       "      <td>形象</td>\n",
       "      <td>外围</td>\n",
       "      <td>综合体内商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>270570</th>\n",
       "      <td>2023-12-28</td>\n",
       "      <td>陕西西安高新万达形象店</td>\n",
       "      <td>31139.792</td>\n",
       "      <td>22893.9117</td>\n",
       "      <td>2022-12-28</td>\n",
       "      <td>NaT</td>\n",
       "      <td>14152.6952</td>\n",
       "      <td>NaN</td>\n",
       "      <td>西安</td>\n",
       "      <td>形象</td>\n",
       "      <td>外围</td>\n",
       "      <td>综合体内商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>270571</th>\n",
       "      <td>2023-12-29</td>\n",
       "      <td>陕西西安高新万达形象店</td>\n",
       "      <td>24274.915</td>\n",
       "      <td>22832.1805</td>\n",
       "      <td>2022-12-29</td>\n",
       "      <td>NaT</td>\n",
       "      <td>14228.6312</td>\n",
       "      <td>NaN</td>\n",
       "      <td>西安</td>\n",
       "      <td>形象</td>\n",
       "      <td>外围</td>\n",
       "      <td>综合体内商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>270572</th>\n",
       "      <td>2023-12-30</td>\n",
       "      <td>陕西西安高新万达形象店</td>\n",
       "      <td>48368.741</td>\n",
       "      <td>23514.5347</td>\n",
       "      <td>2022-12-30</td>\n",
       "      <td>NaT</td>\n",
       "      <td>14431.2110</td>\n",
       "      <td>NaN</td>\n",
       "      <td>西安</td>\n",
       "      <td>形象</td>\n",
       "      <td>外围</td>\n",
       "      <td>综合体内商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>270573</th>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>陕西西安高新万达形象店</td>\n",
       "      <td>40746.583</td>\n",
       "      <td>23866.6524</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>NaT</td>\n",
       "      <td>14641.1328</td>\n",
       "      <td>NaN</td>\n",
       "      <td>西安</td>\n",
       "      <td>形象</td>\n",
       "      <td>外围</td>\n",
       "      <td>综合体内商圈</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>270574 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               日期         门店名称         毛利       AVG60   去年同期(日期) 前年同期(日期)  \\\n",
       "0      2021-09-03      云南丽江形象店   4418.792   4418.7920        NaT      NaT   \n",
       "1      2021-09-04      云南丽江形象店   2149.805   3284.2985        NaT      NaT   \n",
       "2      2021-09-05      云南丽江形象店   3684.275   3417.6240        NaT      NaT   \n",
       "3      2021-09-06      云南丽江形象店   1356.160   2902.2580        NaT      NaT   \n",
       "4      2021-09-07      云南丽江形象店   1119.390   2545.6844        NaT      NaT   \n",
       "...           ...          ...        ...         ...        ...      ...   \n",
       "270569 2023-12-27  陕西西安高新万达形象店   9389.107  23000.2164 2022-12-27      NaT   \n",
       "270570 2023-12-28  陕西西安高新万达形象店  31139.792  22893.9117 2022-12-28      NaT   \n",
       "270571 2023-12-29  陕西西安高新万达形象店  24274.915  22832.1805 2022-12-29      NaT   \n",
       "270572 2023-12-30  陕西西安高新万达形象店  48368.741  23514.5347 2022-12-30      NaT   \n",
       "270573 2023-12-31  陕西西安高新万达形象店  40746.583  23866.6524 2022-12-31      NaT   \n",
       "\n",
       "        去年同期(AVG60)  前年同期(AVG60) 企业部门 企业体系 区域类别     区域商圈  \n",
       "0               NaN          NaN   云南   形象   二级  街铺-复合商圈  \n",
       "1               NaN          NaN   云南   形象   二级  街铺-复合商圈  \n",
       "2               NaN          NaN   云南   形象   二级  街铺-复合商圈  \n",
       "3               NaN          NaN   云南   形象   二级  街铺-复合商圈  \n",
       "4               NaN          NaN   云南   形象   二级  街铺-复合商圈  \n",
       "...             ...          ...  ...  ...  ...      ...  \n",
       "270569   14431.6945          NaN   西安   形象   外围   综合体内商圈  \n",
       "270570   14152.6952          NaN   西安   形象   外围   综合体内商圈  \n",
       "270571   14228.6312          NaN   西安   形象   外围   综合体内商圈  \n",
       "270572   14431.2110          NaN   西安   形象   外围   综合体内商圈  \n",
       "270573   14641.1328          NaN   西安   形象   外围   综合体内商圈  \n",
       "\n",
       "[270574 rows x 12 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 数据计算: 毛利(AVG60)。\n",
    "# --------------------------------------------------\n",
    "SourceDataPDF = Stat_StoreProfitPDF.copy() # -> 拉取毛利所需的数据源。\n",
    "\n",
    "# 筛选标记。\n",
    "# ..................................................\n",
    "SourceDataPDF = SourceDataPDF[SourceDataPDF[\"标记\"] != \"Invalid\"]\n",
    "SourceDataPDF = SourceDataPDF[SourceDataPDF[\"统计月日\"] != \"02-29\"]\n",
    "\n",
    "# 空值填充 (Optional)。\n",
    "# ..................................................\n",
    "#SourceDataPDF[\"毛利\"] = SourceDataPDF[\"毛利\"].fillna(value=float(0.0))\n",
    "\n",
    "# 排序/分组字段。\n",
    "# ..................................................\n",
    "Sort_Fields:list  = [\"门店名称\", \"日期\"]\n",
    "Group_Fields:list = [\"门店名称\"]\n",
    "\n",
    "# 数据聚合。\n",
    "# ..................................................\n",
    "ProfitAvg60PDF = SourceDataPDF[[\"日期\", \"门店名称\", \"毛利\"]].sort_values(Sort_Fields, ascending=True)\n",
    "# ..................................................\n",
    "Grouped = ProfitAvg60PDF.sort_values(Sort_Fields, ascending=True).groupby(Group_Fields, as_index=False)\n",
    "# ..................................................\n",
    "RollingAvg60PDF = Grouped[\"毛利\"].rolling(window=60, min_periods=1).mean(numeric_only=True)\n",
    "DateShift365PDF = Grouped[\"日期\"].shift(365)\n",
    "DateShift730PDF = Grouped[\"日期\"].shift(730)\n",
    "# ..................................................\n",
    "ProfitAvg60PDF[\"AVG60\"] = round(RollingAvg60PDF[\"毛利\"], 4)\n",
    "ProfitAvg60PDF[\"去年同期(日期)\"] = DateShift365PDF[\"日期\"]\n",
    "ProfitAvg60PDF[\"前年同期(日期)\"] = DateShift730PDF[\"日期\"]\n",
    "\n",
    "# 数据聚合: 二次聚合。\n",
    "# ..................................................\n",
    "Grouped = ProfitAvg60PDF.sort_values(Sort_Fields, ascending=True).groupby(Group_Fields, as_index=False)\n",
    "# ..................................................\n",
    "ProfitShift365PDF = Grouped[\"AVG60\"].shift(365)\n",
    "ProfitShift730PDF = Grouped[\"AVG60\"].shift(730)\n",
    "## ..................................................\n",
    "ProfitAvg60PDF[\"去年同期(AVG60)\"] = ProfitShift365PDF[\"AVG60\"]\n",
    "ProfitAvg60PDF[\"前年同期(AVG60)\"] = ProfitShift730PDF[\"AVG60\"]\n",
    "\n",
    "# 数据连接: 外连接对应部门和区域。\n",
    "# ..................................................\n",
    "LEFTPDF = ProfitAvg60PDF\n",
    "RIGHPDF = Stat_StoreDepartRegionPDF\n",
    "# ..................................................\n",
    "TMPPDF = pd.merge(left=LEFTPDF, right=RIGHPDF, how=\"left\", on=\"门店名称\")\n",
    "# ..................................................\n",
    "ProfitAvg60PDF = TMPPDF\n",
    "\n",
    "# 保存数据。\n",
    "# ..................................................\n",
    "ProfitAvg60PDF.to_csv(\"./Datas/BI数据_讯捷集团_数据分析(门店毛利AVG60).csv\", encoding=\"utf-8\", index=False)\n",
    "\n",
    "# ##################################################\n",
    "ProfitAvg60PDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "58999968",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>门店名称</th>\n",
       "      <th>统计年份</th>\n",
       "      <th>门店毛利(年日均)</th>\n",
       "      <th>去年毛利(年日均)</th>\n",
       "      <th>前年毛利(年日均)</th>\n",
       "      <th>今年比去年(增长率)</th>\n",
       "      <th>去年比前年(增长率)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>云南丽江形象店</td>\n",
       "      <td>2021</td>\n",
       "      <td>3834.9584</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>云南丽江形象店</td>\n",
       "      <td>2022</td>\n",
       "      <td>5396.4527</td>\n",
       "      <td>3834.9584</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.407174</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>云南丽江形象店</td>\n",
       "      <td>2023</td>\n",
       "      <td>7925.7926</td>\n",
       "      <td>5396.4527</td>\n",
       "      <td>3834.9584</td>\n",
       "      <td>0.468704</td>\n",
       "      <td>0.407174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>云南保山吾悦广场形象店</td>\n",
       "      <td>2023</td>\n",
       "      <td>5828.9917</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>云南保山形象店</td>\n",
       "      <td>2021</td>\n",
       "      <td>6575.2104</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>785</th>\n",
       "      <td>陕西西安金花路形象店</td>\n",
       "      <td>2022</td>\n",
       "      <td>13356.2281</td>\n",
       "      <td>11417.1906</td>\n",
       "      <td>9449.2904</td>\n",
       "      <td>0.169835</td>\n",
       "      <td>0.208259</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>786</th>\n",
       "      <td>陕西西安金花路形象店</td>\n",
       "      <td>2023</td>\n",
       "      <td>11339.6533</td>\n",
       "      <td>13356.2281</td>\n",
       "      <td>11417.1906</td>\n",
       "      <td>-0.150984</td>\n",
       "      <td>0.169835</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>787</th>\n",
       "      <td>陕西西安长安形象店</td>\n",
       "      <td>2023</td>\n",
       "      <td>7524.0814</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>788</th>\n",
       "      <td>陕西西安高新万达形象店</td>\n",
       "      <td>2022</td>\n",
       "      <td>13928.6708</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>789</th>\n",
       "      <td>陕西西安高新万达形象店</td>\n",
       "      <td>2023</td>\n",
       "      <td>24103.8761</td>\n",
       "      <td>13928.6708</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.730522</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>790 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            门店名称  统计年份   门店毛利(年日均)   去年毛利(年日均)   前年毛利(年日均)  今年比去年(增长率)  \\\n",
       "0        云南丽江形象店  2021   3834.9584         NaN         NaN         NaN   \n",
       "1        云南丽江形象店  2022   5396.4527   3834.9584         NaN    0.407174   \n",
       "2        云南丽江形象店  2023   7925.7926   5396.4527   3834.9584    0.468704   \n",
       "3    云南保山吾悦广场形象店  2023   5828.9917         NaN         NaN         NaN   \n",
       "4        云南保山形象店  2021   6575.2104         NaN         NaN         NaN   \n",
       "..           ...   ...         ...         ...         ...         ...   \n",
       "785   陕西西安金花路形象店  2022  13356.2281  11417.1906   9449.2904    0.169835   \n",
       "786   陕西西安金花路形象店  2023  11339.6533  13356.2281  11417.1906   -0.150984   \n",
       "787    陕西西安长安形象店  2023   7524.0814         NaN         NaN         NaN   \n",
       "788  陕西西安高新万达形象店  2022  13928.6708         NaN         NaN         NaN   \n",
       "789  陕西西安高新万达形象店  2023  24103.8761  13928.6708         NaN    0.730522   \n",
       "\n",
       "     去年比前年(增长率)  \n",
       "0           NaN  \n",
       "1           NaN  \n",
       "2      0.407174  \n",
       "3           NaN  \n",
       "4           NaN  \n",
       "..          ...  \n",
       "785    0.208259  \n",
       "786    0.169835  \n",
       "787         NaN  \n",
       "788         NaN  \n",
       "789         NaN  \n",
       "\n",
       "[790 rows x 7 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 数据计算: 门店毛利(年日均)。\n",
    "# --------------------------------------------------\n",
    "SourceDataPDF = Stat_StoreProfitPDF.copy() # -> 拉取毛利所需的数据源。\n",
    "\n",
    "# 筛选标记。\n",
    "# ..................................................\n",
    "SourceDataPDF = SourceDataPDF[SourceDataPDF[\"标记\"] != \"Invalid\"]\n",
    "SourceDataPDF = SourceDataPDF[SourceDataPDF[\"统计月日\"] != \"02-29\"]\n",
    "\n",
    "# 空值填充 (Optional)。\n",
    "# ..................................................\n",
    "#SourceDataPDF[\"毛利\"] = SourceDataPDF[\"毛利\"].fillna(value=float(0.0))\n",
    "\n",
    "# 数据聚合。\n",
    "# ..................................................\n",
    "TMPPDF = SourceDataPDF[[\"统计年份\", \"门店名称\", \"毛利\"]].sort_values([\"门店名称\", \"统计年份\"], ascending=True)\n",
    "# ..................................................\n",
    "Grouped = TMPPDF.sort_values([\"门店名称\", \"统计年份\"], ascending=True).groupby([\"门店名称\", \"统计年份\"], as_index=False)\n",
    "# ..................................................\n",
    "ProfitYearDaysAvgPDF = Grouped.mean(numeric_only=True)\n",
    "ProfitYearDaysAvgPDF = ProfitYearDaysAvgPDF.rename(columns={\"毛利\":\"门店毛利(年日均)\"})\n",
    "ProfitYearDaysAvgPDF[\"门店毛利(年日均)\"] = round(ProfitYearDaysAvgPDF[\"门店毛利(年日均)\"], 4)\n",
    "\n",
    "# 数据聚合: 二次聚合。\n",
    "# ..................................................\n",
    "TMPPDF = ProfitYearDaysAvgPDF\n",
    "# ..................................................\n",
    "Grouped = TMPPDF.sort_values([\"门店名称\", \"统计年份\"], ascending=True).groupby([\"门店名称\"], as_index=False)\n",
    "# ..................................................\n",
    "Shift1YearDaysAvgPDF = Grouped[\"门店毛利(年日均)\"].shift(1)\n",
    "Shift2YearDaysAvgPDF = Grouped[\"门店毛利(年日均)\"].shift(2)\n",
    "# ..................................................\n",
    "TMPPDF[\"去年毛利(年日均)\"] = Shift1YearDaysAvgPDF[\"门店毛利(年日均)\"]\n",
    "TMPPDF[\"前年毛利(年日均)\"] = Shift2YearDaysAvgPDF[\"门店毛利(年日均)\"]\n",
    "TMPPDF[\"今年比去年(增长率)\"] = (TMPPDF[\"门店毛利(年日均)\"] - TMPPDF[\"去年毛利(年日均)\"]) / TMPPDF[\"去年毛利(年日均)\"]\n",
    "TMPPDF[\"去年比前年(增长率)\"] = (TMPPDF[\"去年毛利(年日均)\"] - TMPPDF[\"前年毛利(年日均)\"]) / TMPPDF[\"前年毛利(年日均)\"]\n",
    "# ..................................................\n",
    "StoreProfitYearDaysAvgPDF = TMPPDF\n",
    "\n",
    "# 保存数据。\n",
    "# ..................................................\n",
    "StoreProfitYearDaysAvgPDF.to_csv(\"./Datas/BI数据_讯捷集团_数据分析(门店毛利年日均).csv\", encoding=\"utf-8\", index=False)\n",
    "\n",
    "StoreProfitYearDaysAvgPDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "ab61f3d4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>统计年份</th>\n",
       "      <th>企业部门</th>\n",
       "      <th>企业体系</th>\n",
       "      <th>门店名称</th>\n",
       "      <th>门店开店日期</th>\n",
       "      <th>门店闭店日期</th>\n",
       "      <th>区域类别</th>\n",
       "      <th>区域商圈</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7928</th>\n",
       "      <td>2023</td>\n",
       "      <td>四部</td>\n",
       "      <td>形象</td>\n",
       "      <td>四川内江资中形象店</td>\n",
       "      <td>2021-07-02</td>\n",
       "      <td>2023-11-04</td>\n",
       "      <td>二级</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11969</th>\n",
       "      <td>2023</td>\n",
       "      <td>重庆</td>\n",
       "      <td>形象</td>\n",
       "      <td>重庆石桥铺形象店</td>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>2023-11-22</td>\n",
       "      <td>外围</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30602</th>\n",
       "      <td>2023</td>\n",
       "      <td>西安</td>\n",
       "      <td>形象</td>\n",
       "      <td>陕西西安东大街形象店</td>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>2023-08-12</td>\n",
       "      <td>市区</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60355</th>\n",
       "      <td>2023</td>\n",
       "      <td>云南</td>\n",
       "      <td>形象</td>\n",
       "      <td>云南昆明南疆形象店</td>\n",
       "      <td>2020-07-27</td>\n",
       "      <td>2023-04-18</td>\n",
       "      <td>市区</td>\n",
       "      <td>街铺-通讯商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70287</th>\n",
       "      <td>2023</td>\n",
       "      <td>四部</td>\n",
       "      <td>形象</td>\n",
       "      <td>四川成都光华形象店</td>\n",
       "      <td>2014-08-01</td>\n",
       "      <td>2023-03-30</td>\n",
       "      <td>外围</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73421</th>\n",
       "      <td>2023</td>\n",
       "      <td>一部</td>\n",
       "      <td>华为</td>\n",
       "      <td>四川大邑东街华为店</td>\n",
       "      <td>2019-08-31</td>\n",
       "      <td>2023-02-13</td>\n",
       "      <td>郊县</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96926</th>\n",
       "      <td>2022</td>\n",
       "      <td>一部</td>\n",
       "      <td>华为</td>\n",
       "      <td>四川成都184号华为店</td>\n",
       "      <td>2016-08-13</td>\n",
       "      <td>2022-11-23</td>\n",
       "      <td>市区</td>\n",
       "      <td>街铺-通讯商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154570</th>\n",
       "      <td>2022</td>\n",
       "      <td>重庆</td>\n",
       "      <td>形象</td>\n",
       "      <td>重庆北碚万达形象店</td>\n",
       "      <td>2017-08-15</td>\n",
       "      <td>2022-03-25</td>\n",
       "      <td>外围</td>\n",
       "      <td>综合体内商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160940</th>\n",
       "      <td>2022</td>\n",
       "      <td>四部</td>\n",
       "      <td>形象</td>\n",
       "      <td>四川龙泉通达形象店</td>\n",
       "      <td>2016-03-20</td>\n",
       "      <td>2022-02-20</td>\n",
       "      <td>郊县</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        统计年份 企业部门 企业体系         门店名称     门店开店日期     门店闭店日期 区域类别     区域商圈\n",
       "7928    2023   四部   形象    四川内江资中形象店 2021-07-02 2023-11-04   二级  街铺-复合商圈\n",
       "11969   2023   重庆   形象     重庆石桥铺形象店 2014-01-01 2023-11-22   外围  街铺-复合商圈\n",
       "30602   2023   西安   形象   陕西西安东大街形象店 2014-01-01 2023-08-12   市区  街铺-复合商圈\n",
       "60355   2023   云南   形象    云南昆明南疆形象店 2020-07-27 2023-04-18   市区  街铺-通讯商圈\n",
       "70287   2023   四部   形象    四川成都光华形象店 2014-08-01 2023-03-30   外围  街铺-复合商圈\n",
       "73421   2023   一部   华为    四川大邑东街华为店 2019-08-31 2023-02-13   郊县  街铺-复合商圈\n",
       "96926   2022   一部   华为  四川成都184号华为店 2016-08-13 2022-11-23   市区  街铺-通讯商圈\n",
       "154570  2022   重庆   形象    重庆北碚万达形象店 2017-08-15 2022-03-25   外围   综合体内商圈\n",
       "160940  2022   四部   形象    四川龙泉通达形象店 2016-03-20 2022-02-20   郊县  街铺-复合商圈"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 数据计算: 闭店门店。\n",
    "# --------------------------------------------------\n",
    "SourceDataPDF = Stat_StoreProfitPDF.copy() # -> 拉取毛利所需的数据源。\n",
    "\n",
    "# 筛选标记。\n",
    "# --------------------------------------------------\n",
    "SourceDataPDF = SourceDataPDF[SourceDataPDF[\"标记\"] != \"Invalid\"]\n",
    "# ..................................................\n",
    "SourceDataPDF = SourceDataPDF[[\"统计年份\",\"企业部门\",\"企业体系\",\"门店名称\",\"门店开店日期\",\"门店闭店日期\",\"区域类别\",\"区域商圈\"]]\n",
    "# ..................................................\n",
    "SourceDataPDF = SourceDataPDF[SourceDataPDF[\"门店闭店日期\"].isna() == False]\n",
    "# ..................................................\n",
    "ClosedStorePDF = SourceDataPDF\n",
    "\n",
    "# ##################################################\n",
    "ClosedStorePDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "8cd70a44",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>统计年份</th>\n",
       "      <th>企业部门</th>\n",
       "      <th>企业体系</th>\n",
       "      <th>区域类别</th>\n",
       "      <th>区域商圈</th>\n",
       "      <th>区域毛利(年日均)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020</td>\n",
       "      <td>一部</td>\n",
       "      <td>华为</td>\n",
       "      <td>外围</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>7849.780000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020</td>\n",
       "      <td>一部</td>\n",
       "      <td>华为</td>\n",
       "      <td>市区</td>\n",
       "      <td>街铺-通讯商圈</td>\n",
       "      <td>7712.713563</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020</td>\n",
       "      <td>一部</td>\n",
       "      <td>华为</td>\n",
       "      <td>郊县</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>6344.892605</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020</td>\n",
       "      <td>一部</td>\n",
       "      <td>华为</td>\n",
       "      <td>郊县</td>\n",
       "      <td>街铺-通讯商圈</td>\n",
       "      <td>14195.877172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020</td>\n",
       "      <td>一部</td>\n",
       "      <td>形象</td>\n",
       "      <td>二级</td>\n",
       "      <td>综合体外商圈</td>\n",
       "      <td>5505.282948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>357</th>\n",
       "      <td>2023</td>\n",
       "      <td>重庆</td>\n",
       "      <td>形象</td>\n",
       "      <td>市区</td>\n",
       "      <td>综合体内商圈</td>\n",
       "      <td>12441.553311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>358</th>\n",
       "      <td>2023</td>\n",
       "      <td>重庆</td>\n",
       "      <td>形象</td>\n",
       "      <td>市区</td>\n",
       "      <td>综合体外商圈</td>\n",
       "      <td>5475.977429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>359</th>\n",
       "      <td>2023</td>\n",
       "      <td>重庆</td>\n",
       "      <td>形象</td>\n",
       "      <td>市区</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>11764.656781</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>360</th>\n",
       "      <td>2023</td>\n",
       "      <td>重庆</td>\n",
       "      <td>形象</td>\n",
       "      <td>郊县</td>\n",
       "      <td>综合体外商圈</td>\n",
       "      <td>7783.639532</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>361</th>\n",
       "      <td>2023</td>\n",
       "      <td>重庆</td>\n",
       "      <td>形象</td>\n",
       "      <td>郊县</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>7193.645343</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>362 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     统计年份 企业部门 企业体系 区域类别     区域商圈     区域毛利(年日均)\n",
       "0    2020   一部   华为   外围  街铺-复合商圈   7849.780000\n",
       "1    2020   一部   华为   市区  街铺-通讯商圈   7712.713563\n",
       "2    2020   一部   华为   郊县  街铺-复合商圈   6344.892605\n",
       "3    2020   一部   华为   郊县  街铺-通讯商圈  14195.877172\n",
       "4    2020   一部   形象   二级   综合体外商圈   5505.282948\n",
       "..    ...  ...  ...  ...      ...           ...\n",
       "357  2023   重庆   形象   市区   综合体内商圈  12441.553311\n",
       "358  2023   重庆   形象   市区   综合体外商圈   5475.977429\n",
       "359  2023   重庆   形象   市区  街铺-复合商圈  11764.656781\n",
       "360  2023   重庆   形象   郊县   综合体外商圈   7783.639532\n",
       "361  2023   重庆   形象   郊县  街铺-复合商圈   7193.645343\n",
       "\n",
       "[362 rows x 6 columns]"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 数据计算: 区域毛利(年日均)。\n",
    "# --------------------------------------------------\n",
    "SourceDataPDF = Stat_StoreProfitPDF.copy() # -> 拉取毛利所需的数据源。\n",
    "\n",
    "# 筛选标记。\n",
    "# ..................................................\n",
    "SourceDataPDF = SourceDataPDF[SourceDataPDF[\"标记\"] != \"Invalid\"]\n",
    "\n",
    "# 数据聚合。\n",
    "# ..................................................\n",
    "TMPPDF = SourceDataPDF[[\"统计年份\", \"企业部门\", \"企业体系\", \"区域类别\", \"区域商圈\", \"毛利\"]]\n",
    "# ..................................................\n",
    "TMPPDF = TMPPDF.groupby([\"统计年份\", \"企业部门\", \"企业体系\", \"区域类别\", \"区域商圈\"], as_index=False).mean(\"毛利\")\n",
    "# ..................................................\n",
    "TMPPDF = TMPPDF.rename(columns={\"毛利\":\"区域毛利(年日均)\"})\n",
    "# ..................................................\n",
    "RegionProfitYearDaysAvgPDF = TMPPDF\n",
    "\n",
    "# ##################################################\n",
    "RegionProfitYearDaysAvgPDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "7aaa5560",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>统计年份</th>\n",
       "      <th>企业部门</th>\n",
       "      <th>企业体系</th>\n",
       "      <th>门店名称</th>\n",
       "      <th>门店开店日期</th>\n",
       "      <th>区域类别</th>\n",
       "      <th>区域商圈</th>\n",
       "      <th>门店闭店日期</th>\n",
       "      <th>门店开店年份</th>\n",
       "      <th>门店闭店年份</th>\n",
       "      <th>区域描述</th>\n",
       "      <th>在开门店BOOL</th>\n",
       "      <th>已关门店BOOL</th>\n",
       "      <th>在开/已关状态</th>\n",
       "      <th>门店BOOL</th>\n",
       "      <th>门店等级</th>\n",
       "      <th>区域毛利(年日均)</th>\n",
       "      <th>门店毛利(年日均)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023</td>\n",
       "      <td>贵州</td>\n",
       "      <td>形象</td>\n",
       "      <td>贵州毕节黔西形象店</td>\n",
       "      <td>2023-03-26</td>\n",
       "      <td>外围</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>外围/街铺-复合商圈</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>在开</td>\n",
       "      <td>1</td>\n",
       "      <td>C</td>\n",
       "      <td>6345.704925</td>\n",
       "      <td>3311.8095</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023</td>\n",
       "      <td>贵州</td>\n",
       "      <td>华为</td>\n",
       "      <td>贵州开阳华为店</td>\n",
       "      <td>2016-12-05</td>\n",
       "      <td>二级</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>二级/街铺-复合商圈</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>在开</td>\n",
       "      <td>1</td>\n",
       "      <td>C</td>\n",
       "      <td>5293.401920</td>\n",
       "      <td>3043.1708</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023</td>\n",
       "      <td>贵州</td>\n",
       "      <td>形象</td>\n",
       "      <td>贵州遵义丁字口形象店</td>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>二级</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2014</td>\n",
       "      <td>NaN</td>\n",
       "      <td>二级/街铺-复合商圈</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>在开</td>\n",
       "      <td>1</td>\n",
       "      <td>A+1</td>\n",
       "      <td>13402.800260</td>\n",
       "      <td>20123.7962</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023</td>\n",
       "      <td>贵州</td>\n",
       "      <td>形象</td>\n",
       "      <td>贵州贵阳黄河形象店</td>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>郊县</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2014</td>\n",
       "      <td>NaN</td>\n",
       "      <td>郊县/街铺-复合商圈</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>在开</td>\n",
       "      <td>1</td>\n",
       "      <td>A1</td>\n",
       "      <td>13661.996864</td>\n",
       "      <td>9941.2350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023</td>\n",
       "      <td>贵州</td>\n",
       "      <td>形象</td>\n",
       "      <td>贵州凯里新世纪形象店</td>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>二级</td>\n",
       "      <td>街铺-通讯商圈</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2014</td>\n",
       "      <td>NaN</td>\n",
       "      <td>二级/街铺-通讯商圈</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>在开</td>\n",
       "      <td>1</td>\n",
       "      <td>A2</td>\n",
       "      <td>15085.075179</td>\n",
       "      <td>17897.0586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>785</th>\n",
       "      <td>2020</td>\n",
       "      <td>西安</td>\n",
       "      <td>华为</td>\n",
       "      <td>陕西咸阳万达华为店</td>\n",
       "      <td>2018-09-30</td>\n",
       "      <td>二级</td>\n",
       "      <td>综合体内商圈</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2018</td>\n",
       "      <td>NaN</td>\n",
       "      <td>二级/综合体内商圈</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>在开</td>\n",
       "      <td>1</td>\n",
       "      <td>B2</td>\n",
       "      <td>6165.211752</td>\n",
       "      <td>6159.9135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>786</th>\n",
       "      <td>2020</td>\n",
       "      <td>四部</td>\n",
       "      <td>形象</td>\n",
       "      <td>四川新都桂湖东路形象店</td>\n",
       "      <td>2014-08-01</td>\n",
       "      <td>郊县</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2014</td>\n",
       "      <td>NaN</td>\n",
       "      <td>郊县/街铺-复合商圈</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>在开</td>\n",
       "      <td>1</td>\n",
       "      <td>A1</td>\n",
       "      <td>9168.912522</td>\n",
       "      <td>7446.0285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>787</th>\n",
       "      <td>2020</td>\n",
       "      <td>云南</td>\n",
       "      <td>形象</td>\n",
       "      <td>云南昆明白云形象店</td>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>市区</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2014</td>\n",
       "      <td>NaN</td>\n",
       "      <td>市区/街铺-复合商圈</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>在开</td>\n",
       "      <td>1</td>\n",
       "      <td>A1</td>\n",
       "      <td>8314.906490</td>\n",
       "      <td>9781.0885</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>788</th>\n",
       "      <td>2020</td>\n",
       "      <td>一部</td>\n",
       "      <td>形象</td>\n",
       "      <td>四川自贡龙都形象店</td>\n",
       "      <td>2014-01-01</td>\n",
       "      <td>二级</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2014</td>\n",
       "      <td>NaN</td>\n",
       "      <td>二级/街铺-复合商圈</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>在开</td>\n",
       "      <td>1</td>\n",
       "      <td>B1</td>\n",
       "      <td>7669.217006</td>\n",
       "      <td>7382.2136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>789</th>\n",
       "      <td>2020</td>\n",
       "      <td>重庆</td>\n",
       "      <td>形象</td>\n",
       "      <td>重庆两路金港国际形象店</td>\n",
       "      <td>2020-12-13</td>\n",
       "      <td>外围</td>\n",
       "      <td>综合体内商圈</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2020</td>\n",
       "      <td>NaN</td>\n",
       "      <td>外围/综合体内商圈</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>在开</td>\n",
       "      <td>1</td>\n",
       "      <td>B1</td>\n",
       "      <td>5503.767193</td>\n",
       "      <td>6174.6979</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>790 rows × 18 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     统计年份 企业部门 企业体系         门店名称     门店开店日期 区域类别     区域商圈 门店闭店日期  门店开店年份  \\\n",
       "0    2023   贵州   形象    贵州毕节黔西形象店 2023-03-26   外围  街铺-复合商圈    NaT    2023   \n",
       "1    2023   贵州   华为      贵州开阳华为店 2016-12-05   二级  街铺-复合商圈    NaT    2016   \n",
       "2    2023   贵州   形象   贵州遵义丁字口形象店 2014-01-01   二级  街铺-复合商圈    NaT    2014   \n",
       "3    2023   贵州   形象    贵州贵阳黄河形象店 2014-01-01   郊县  街铺-复合商圈    NaT    2014   \n",
       "4    2023   贵州   形象   贵州凯里新世纪形象店 2014-01-01   二级  街铺-通讯商圈    NaT    2014   \n",
       "..    ...  ...  ...          ...        ...  ...      ...    ...     ...   \n",
       "785  2020   西安   华为    陕西咸阳万达华为店 2018-09-30   二级   综合体内商圈    NaT    2018   \n",
       "786  2020   四部   形象  四川新都桂湖东路形象店 2014-08-01   郊县  街铺-复合商圈    NaT    2014   \n",
       "787  2020   云南   形象    云南昆明白云形象店 2014-01-01   市区  街铺-复合商圈    NaT    2014   \n",
       "788  2020   一部   形象    四川自贡龙都形象店 2014-01-01   二级  街铺-复合商圈    NaT    2014   \n",
       "789  2020   重庆   形象  重庆两路金港国际形象店 2020-12-13   外围   综合体内商圈    NaT    2020   \n",
       "\n",
       "     门店闭店年份        区域描述  在开门店BOOL  已关门店BOOL 在开/已关状态  门店BOOL 门店等级  \\\n",
       "0       NaN  外围/街铺-复合商圈         1         0      在开       1    C   \n",
       "1       NaN  二级/街铺-复合商圈         1         0      在开       1    C   \n",
       "2       NaN  二级/街铺-复合商圈         1         0      在开       1  A+1   \n",
       "3       NaN  郊县/街铺-复合商圈         1         0      在开       1   A1   \n",
       "4       NaN  二级/街铺-通讯商圈         1         0      在开       1   A2   \n",
       "..      ...         ...       ...       ...     ...     ...  ...   \n",
       "785     NaN   二级/综合体内商圈         1         0      在开       1   B2   \n",
       "786     NaN  郊县/街铺-复合商圈         1         0      在开       1   A1   \n",
       "787     NaN  市区/街铺-复合商圈         1         0      在开       1   A1   \n",
       "788     NaN  二级/街铺-复合商圈         1         0      在开       1   B1   \n",
       "789     NaN   外围/综合体内商圈         1         0      在开       1   B1   \n",
       "\n",
       "        区域毛利(年日均)   门店毛利(年日均)  \n",
       "0     6345.704925   3311.8095  \n",
       "1     5293.401920   3043.1708  \n",
       "2    13402.800260  20123.7962  \n",
       "3    13661.996864   9941.2350  \n",
       "4    15085.075179  17897.0586  \n",
       "..            ...         ...  \n",
       "785   6165.211752   6159.9135  \n",
       "786   9168.912522   7446.0285  \n",
       "787   8314.906490   9781.0885  \n",
       "788   7669.217006   7382.2136  \n",
       "789   5503.767193   6174.6979  \n",
       "\n",
       "[790 rows x 18 columns]"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 数据筛选: 门店总览。\n",
    "# --------------------------------------------------\n",
    "StoreOverviewPDF = Stat_StoreProfitPDF.copy()\n",
    "\n",
    "# 筛选标记。\n",
    "# --------------------------------------------------\n",
    "StoreOverviewPDF = StoreOverviewPDF[StoreOverviewPDF[\"标记\"] != \"Invalid\"]\n",
    "StoreOverviewPDF = StoreOverviewPDF[[\"统计年份\",\"企业部门\",\"企业体系\",\"门店名称\",\"门店开店日期\",\"区域类别\",\"区域商圈\"]]\n",
    "\n",
    "# 数据去重。\n",
    "# --------------------------------------------------\n",
    "StoreOverviewPDF = StoreOverviewPDF.drop_duplicates(subset=[\"统计年份\", \"企业部门\", \"企业体系\", \"门店名称\", \"门店开店日期\"])\n",
    "\n",
    "# 数据连接: 外连接对应门店闭店日期。\n",
    "# --------------------------------------------------\n",
    "LEFTPDF = StoreOverviewPDF\n",
    "RIGHPDF = ClosedStorePDF[[\"统计年份\", \"企业部门\", \"企业体系\", \"门店名称\", \"门店闭店日期\"]]\n",
    "# ..................................................\n",
    "TMPPDF = pd.merge(left=LEFTPDF, right=RIGHPDF, how=\"left\", on=[\"统计年份\", \"企业部门\", \"企业体系\", \"门店名称\"])\n",
    "# ..................................................\n",
    "StoreOverviewPDF = TMPPDF\n",
    "\n",
    "# 数据提取。\n",
    "# --------------------------------------------------\n",
    "StoreOverviewPDF[\"门店开店年份\"] = StoreOverviewPDF[\"门店开店日期\"].dt.year\n",
    "StoreOverviewPDF[\"门店闭店年份\"] = StoreOverviewPDF[\"门店闭店日期\"].dt.year\n",
    "\n",
    "# 数据计算。\n",
    "# --------------------------------------------------\n",
    "StoreOverviewPDF[\"区域描述\"] = StoreOverviewPDF[\"区域类别\"] + str(\"/\") + \\\n",
    "                               StoreOverviewPDF[\"区域商圈\"]\n",
    "\n",
    "# 数据计算: 在开门店占位数量 1 和已关门店占位数量 1。\n",
    "# --------------------------------------------------\n",
    "StoreOverviewPDF[\"在开门店BOOL\"] = int(0)\n",
    "StoreOverviewPDF[\"已关门店BOOL\"] = int(0)\n",
    "StoreOverviewPDF[\"在开/已关状态\"] = None\n",
    "# ..................................................\n",
    "IdxArr = StoreOverviewPDF[StoreOverviewPDF[\"门店闭店日期\"].isna() == True].index\n",
    "# ..................................................\n",
    "StoreOverviewPDF.loc[IdxArr, \"在开门店BOOL\"] = int(1)\n",
    "StoreOverviewPDF.loc[IdxArr, \"在开/已关状态\"] = str(\"在开\")\n",
    "# ..................................................\n",
    "IdxArr = StoreOverviewPDF[StoreOverviewPDF[\"门店闭店日期\"].isna() == False].index\n",
    "# ..................................................\n",
    "StoreOverviewPDF.loc[IdxArr, \"已关门店BOOL\"] = int(1)\n",
    "StoreOverviewPDF.loc[IdxArr, \"在开/已关状态\"] = str(\"已关\")\n",
    "\n",
    "# 数据计算: 所有门店占位数量 1。\n",
    "# --------------------------------------------------\n",
    "StoreOverviewPDF[\"门店BOOL\"] = int(1)\n",
    "\n",
    "# 数据连接: 外连接对应门店等级。\n",
    "# --------------------------------------------------\n",
    "LEFTPDF = StoreOverviewPDF\n",
    "RIGHPDF = Stat_StoreLevelPDF\n",
    "# ..................................................\n",
    "TMPPDF = pd.merge(left=LEFTPDF, right=RIGHPDF, how=\"left\", on=\"门店名称\")\n",
    "# ..................................................\n",
    "StoreOverviewPDF = TMPPDF\n",
    "\n",
    "# 数据连接: 外连接对应区域毛利(年日均)。\n",
    "# --------------------------------------------------\n",
    "LEFTPDF = StoreOverviewPDF\n",
    "RIGHPDF = RegionProfitYearDaysAvgPDF\n",
    "# ..................................................\n",
    "TMPPDF = pd.merge(left=LEFTPDF, right=RIGHPDF, how=\"left\", on=[\"统计年份\", \"企业部门\", \"企业体系\", \"区域类别\", \"区域商圈\"])\n",
    "# ..................................................\n",
    "StoreOverviewPDF = TMPPDF\n",
    "\n",
    "# 数据连接: 外连接对应门店毛利(年日均)。\n",
    "# --------------------------------------------------\n",
    "LEFTPDF = StoreOverviewPDF\n",
    "RIGHPDF = StoreProfitYearDaysAvgPDF[[\"统计年份\", \"门店名称\", \"门店毛利(年日均)\"]]\n",
    "# ..................................................\n",
    "TMPPDF = pd.merge(left=LEFTPDF, right=RIGHPDF, how=\"left\", on=[\"统计年份\", \"门店名称\"])\n",
    "# ..................................................\n",
    "StoreOverviewPDF = TMPPDF\n",
    "\n",
    "# 保存数据。\n",
    "# --------------------------------------------------\n",
    "StoreOverviewPDF.to_csv(\"./Datas/BI数据_讯捷集团_数据分析(门店总览).csv\", encoding=\"utf-8\", index=False)\n",
    "\n",
    "# ##################################################\n",
    "StoreOverviewPDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "3f938221",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>企业部门</th>\n",
       "      <th>企业体系</th>\n",
       "      <th>区域类别</th>\n",
       "      <th>区域商圈</th>\n",
       "      <th>区域毛利</th>\n",
       "      <th>年份</th>\n",
       "      <th>区域描述(简略)</th>\n",
       "      <th>区域描述(详细)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>一部</td>\n",
       "      <td>华为</td>\n",
       "      <td>外围</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>6795.700000</td>\n",
       "      <td>2020</td>\n",
       "      <td>外围/街铺-复合商圈</td>\n",
       "      <td>一部/华为/外围/街铺-复合商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>一部</td>\n",
       "      <td>华为</td>\n",
       "      <td>市区</td>\n",
       "      <td>街铺-通讯商圈</td>\n",
       "      <td>16026.850000</td>\n",
       "      <td>2020</td>\n",
       "      <td>市区/街铺-通讯商圈</td>\n",
       "      <td>一部/华为/市区/街铺-通讯商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>一部</td>\n",
       "      <td>华为</td>\n",
       "      <td>郊县</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>13892.602850</td>\n",
       "      <td>2020</td>\n",
       "      <td>郊县/街铺-复合商圈</td>\n",
       "      <td>一部/华为/郊县/街铺-复合商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>一部</td>\n",
       "      <td>华为</td>\n",
       "      <td>郊县</td>\n",
       "      <td>街铺-通讯商圈</td>\n",
       "      <td>25316.253900</td>\n",
       "      <td>2020</td>\n",
       "      <td>郊县/街铺-通讯商圈</td>\n",
       "      <td>一部/华为/郊县/街铺-通讯商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>一部</td>\n",
       "      <td>形象</td>\n",
       "      <td>二级</td>\n",
       "      <td>综合体外商圈</td>\n",
       "      <td>2977.161600</td>\n",
       "      <td>2020</td>\n",
       "      <td>二级/综合体外商圈</td>\n",
       "      <td>一部/形象/二级/综合体外商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128211</th>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>重庆</td>\n",
       "      <td>形象</td>\n",
       "      <td>市区</td>\n",
       "      <td>综合体内商圈</td>\n",
       "      <td>14790.777700</td>\n",
       "      <td>2023</td>\n",
       "      <td>市区/综合体内商圈</td>\n",
       "      <td>重庆/形象/市区/综合体内商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128212</th>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>重庆</td>\n",
       "      <td>形象</td>\n",
       "      <td>市区</td>\n",
       "      <td>综合体外商圈</td>\n",
       "      <td>9436.358000</td>\n",
       "      <td>2023</td>\n",
       "      <td>市区/综合体外商圈</td>\n",
       "      <td>重庆/形象/市区/综合体外商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128213</th>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>重庆</td>\n",
       "      <td>形象</td>\n",
       "      <td>市区</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>18079.026633</td>\n",
       "      <td>2023</td>\n",
       "      <td>市区/街铺-复合商圈</td>\n",
       "      <td>重庆/形象/市区/街铺-复合商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128214</th>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>重庆</td>\n",
       "      <td>形象</td>\n",
       "      <td>郊县</td>\n",
       "      <td>综合体外商圈</td>\n",
       "      <td>16678.896000</td>\n",
       "      <td>2023</td>\n",
       "      <td>郊县/综合体外商圈</td>\n",
       "      <td>重庆/形象/郊县/综合体外商圈</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128215</th>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>重庆</td>\n",
       "      <td>形象</td>\n",
       "      <td>郊县</td>\n",
       "      <td>街铺-复合商圈</td>\n",
       "      <td>8103.416969</td>\n",
       "      <td>2023</td>\n",
       "      <td>郊县/街铺-复合商圈</td>\n",
       "      <td>重庆/形象/郊县/街铺-复合商圈</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>128216 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               日期 企业部门 企业体系 区域类别     区域商圈          区域毛利    年份    区域描述(简略)  \\\n",
       "0      2020-01-01   一部   华为   外围  街铺-复合商圈   6795.700000  2020  外围/街铺-复合商圈   \n",
       "1      2020-01-01   一部   华为   市区  街铺-通讯商圈  16026.850000  2020  市区/街铺-通讯商圈   \n",
       "2      2020-01-01   一部   华为   郊县  街铺-复合商圈  13892.602850  2020  郊县/街铺-复合商圈   \n",
       "3      2020-01-01   一部   华为   郊县  街铺-通讯商圈  25316.253900  2020  郊县/街铺-通讯商圈   \n",
       "4      2020-01-01   一部   形象   二级   综合体外商圈   2977.161600  2020   二级/综合体外商圈   \n",
       "...           ...  ...  ...  ...      ...           ...   ...         ...   \n",
       "128211 2023-12-31   重庆   形象   市区   综合体内商圈  14790.777700  2023   市区/综合体内商圈   \n",
       "128212 2023-12-31   重庆   形象   市区   综合体外商圈   9436.358000  2023   市区/综合体外商圈   \n",
       "128213 2023-12-31   重庆   形象   市区  街铺-复合商圈  18079.026633  2023  市区/街铺-复合商圈   \n",
       "128214 2023-12-31   重庆   形象   郊县   综合体外商圈  16678.896000  2023   郊县/综合体外商圈   \n",
       "128215 2023-12-31   重庆   形象   郊县  街铺-复合商圈   8103.416969  2023  郊县/街铺-复合商圈   \n",
       "\n",
       "                区域描述(详细)  \n",
       "0       一部/华为/外围/街铺-复合商圈  \n",
       "1       一部/华为/市区/街铺-通讯商圈  \n",
       "2       一部/华为/郊县/街铺-复合商圈  \n",
       "3       一部/华为/郊县/街铺-通讯商圈  \n",
       "4        一部/形象/二级/综合体外商圈  \n",
       "...                  ...  \n",
       "128211   重庆/形象/市区/综合体内商圈  \n",
       "128212   重庆/形象/市区/综合体外商圈  \n",
       "128213  重庆/形象/市区/街铺-复合商圈  \n",
       "128214   重庆/形象/郊县/综合体外商圈  \n",
       "128215  重庆/形象/郊县/街铺-复合商圈  \n",
       "\n",
       "[128216 rows x 9 columns]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 数据计算: 区域毛利。\n",
    "# --------------------------------------------------\n",
    "RegionPDF = Stat_StoreProfitPDF.copy() # -> 拉取毛利所需的数据源。\n",
    "\n",
    "# 筛选标记。\n",
    "# ..................................................\n",
    "RegionPDF = RegionPDF[RegionPDF[\"标记\"] != \"Invalid\"]\n",
    "\n",
    "# 数据聚合。\n",
    "# ..................................................\n",
    "TMPPDF = RegionPDF[[\"日期\", \"企业部门\", \"企业体系\", \"区域类别\", \"区域商圈\", \"毛利\"]]\n",
    "# ..................................................\n",
    "TMPPDF = TMPPDF.groupby([\"日期\", \"企业部门\", \"企业体系\", \"区域类别\", \"区域商圈\"], as_index=False).mean(\"毛利\")\n",
    "# ..................................................\n",
    "TMPPDF = TMPPDF.rename(columns={\"毛利\":\"区域毛利\"})\n",
    "# ..................................................\n",
    "RegionPDF = TMPPDF\n",
    "\n",
    "# 数据提取。\n",
    "# ..................................................\n",
    "RegionPDF[\"年份\"] = RegionPDF[\"日期\"].dt.year\n",
    "\n",
    "# 数据计算。\n",
    "# ..................................................\n",
    "RegionPDF[\"区域描述(简略)\"] = RegionPDF[\"区域类别\"] + str(\"/\") + \\\n",
    "                              RegionPDF[\"区域商圈\"]\n",
    "# ..................................................\n",
    "RegionPDF[\"区域描述(详细)\"] = RegionPDF[\"企业部门\"] + str(\"/\") + \\\n",
    "                              RegionPDF[\"企业体系\"] + str(\"/\") + \\\n",
    "                              RegionPDF[\"区域类别\"] + str(\"/\") + \\\n",
    "                              RegionPDF[\"区域商圈\"]\n",
    "\n",
    "# 保存数据。\n",
    "# ..................................................\n",
    "RegionPDF.to_csv(\"./Datas/BI数据_讯捷集团_数据分析(区域毛利).csv\", encoding=\"utf-8\", index=False)\n",
    "\n",
    "# ##################################################\n",
    "RegionPDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "fe0d5757",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "\"['区域描述'] not in index\"",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[1;32m~\\AppData\\Local\\Temp\\ipykernel_7304\\2139057996.py\u001b[0m in \u001b[0;36m<cell line: 45>\u001b[1;34m()\u001b[0m\n\u001b[0;32m     43\u001b[0m \u001b[1;31m# ..................................................\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     44\u001b[0m \u001b[0mClosedStorePDF\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mconcat\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mClosedStorePDF_1\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mClosedStorePDF_2\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mjoin\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m\"outer\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 45\u001b[1;33m \u001b[0mRegionDataPDF\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mRegionPDF\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m\"日期\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m\"区域描述\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m\"区域毛利\"\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     46\u001b[0m \u001b[0mClosedStorePDF\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mmerge\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mleft\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mRegionDataPDF\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mright\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mClosedStorePDF\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mhow\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m\"inner\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mon\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m\"日期\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m\"区域描述\"\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     47\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\program files\\python-3.8.0-x64\\lib\\site-packages\\pandas\\core\\frame.py\u001b[0m in \u001b[0;36m__getitem__\u001b[1;34m(self, key)\u001b[0m\n\u001b[0;32m   3509\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mis_iterator\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   3510\u001b[0m                 \u001b[0mkey\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mlist\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 3511\u001b[1;33m             \u001b[0mindexer\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_get_indexer_strict\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m\"columns\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m1\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   3512\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   3513\u001b[0m         \u001b[1;31m# take() does not accept boolean indexers\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\program files\\python-3.8.0-x64\\lib\\site-packages\\pandas\\core\\indexes\\base.py\u001b[0m in \u001b[0;36m_get_indexer_strict\u001b[1;34m(self, key, axis_name)\u001b[0m\n\u001b[0;32m   5780\u001b[0m             \u001b[0mkeyarr\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mindexer\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mnew_indexer\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_reindex_non_unique\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkeyarr\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   5781\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 5782\u001b[1;33m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_raise_if_missing\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkeyarr\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mindexer\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0maxis_name\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   5783\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   5784\u001b[0m         \u001b[0mkeyarr\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtake\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mindexer\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\program files\\python-3.8.0-x64\\lib\\site-packages\\pandas\\core\\indexes\\base.py\u001b[0m in \u001b[0;36m_raise_if_missing\u001b[1;34m(self, key, indexer, axis_name)\u001b[0m\n\u001b[0;32m   5843\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   5844\u001b[0m             \u001b[0mnot_found\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mlist\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mensure_index\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mmissing_mask\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mnonzero\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0munique\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 5845\u001b[1;33m             \u001b[1;32mraise\u001b[0m \u001b[0mKeyError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34mf\"{not_found} not in index\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   5846\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   5847\u001b[0m     \u001b[1;33m@\u001b[0m\u001b[0moverload\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mKeyError\u001b[0m: \"['区域描述'] not in index\""
     ]
    }
   ],
   "source": [
    "# 数据计算: 闭店门店日均毛利。\n",
    "# --------------------------------------------------\n",
    "ClosedStorePDF = Stat_StoreProfitPDF.copy() # -> 拉取毛利所需的数据源。\n",
    "\n",
    "# 筛选标记。\n",
    "# ..................................................\n",
    "ClosedStorePDF = ClosedStorePDF[(ClosedStorePDF[\"标记\"] != \"Invalid\")]\n",
    "ClosedStorePDF = ClosedStorePDF[(ClosedStorePDF[\"门店闭店日期\"].isna() == False) & (ClosedStorePDF[\"门店状态\"] == \"闭店\")]\n",
    "ClosedStorePDF = ClosedStorePDF[(ClosedStorePDF[\"门店闭店装修\"] != \"闭装\")]\n",
    "\n",
    "# 数据提取。\n",
    "# ..................................................\n",
    "ClosedStorePDF[\"门店闭店年份\"] = ClosedStorePDF[\"门店闭店日期\"].dt.year.fillna(value=0).astype(\"int64\")\n",
    "ClosedStorePDF[\"门店闭店月份\"] = ClosedStorePDF[\"门店闭店日期\"].dt.month.fillna(value=0).astype(\"int64\")\n",
    "\n",
    "# 数据计算。\n",
    "# ..................................................\n",
    "ClosedStorePDF[\"区域描述(简略)\"] = ClosedStorePDF[\"区域类别\"] + str(\"/\") + \\\n",
    "                                   ClosedStorePDF[\"区域商圈\"]\n",
    "\n",
    "# 数据聚合。\n",
    "# ..................................................\n",
    "ClosedStorePDF_1 = ClosedStorePDF[[\"日期\", \"门店名称\", \"区域描述\", \"毛利\"]].groupby([\"日期\", \"门店名称\", \"区域描述\"], as_index=False).mean(\"毛利\")\n",
    "\n",
    "# 数据计算: 二次计算。\n",
    "# ..................................................\n",
    "ClosedStorePDF_1 = ClosedStorePDF_1.rename(columns={\"区域描述(简略)\":\"区域描述\", \"毛利\":\"门店日均毛利\"})\n",
    "ClosedStorePDF_2 = ClosedStorePDF_2.rename(columns={\"区域描述(详细)\":\"区域描述\", \"毛利\":\"门店日均毛利\"})\n",
    "# ..................................................\n",
    "ClosedStorePDF_1[\"区域描述类型\"] = str(\"简略\")\n",
    "ClosedStorePDF_2[\"区域描述类型\"] = str(\"详细\")\n",
    "# ..................................................\n",
    "ClosedStorePDF_1[\"门店闭店年份\"] = ClosedStorePDF_1[\"日期\"].dt.year\n",
    "ClosedStorePDF_2[\"门店闭店年份\"] = ClosedStorePDF_2[\"日期\"].dt.year\n",
    "\n",
    "# 数据合并。\n",
    "# ..................................................\n",
    "ClosedStorePDF = pd.concat([ClosedStorePDF_1, ClosedStorePDF_2], join=\"outer\")\n",
    "RegionDataPDF = RegionPDF[[\"日期\", \"区域描述\", \"区域毛利\"]]\n",
    "ClosedStorePDF = pd.merge(left=RegionDataPDF, right=ClosedStorePDF, how=\"inner\", on=[\"日期\", \"区域描述\"])\n",
    "\n",
    "# 保存数据。\n",
    "# ..................................................\n",
    "ClosedStorePDF.to_csv(\"./Datas/BI数据_讯捷集团_数据分析(闭店门店日均毛利).csv\", encoding=\"utf-8\", index=False)\n",
    "\n",
    "# ##################################################\n",
    "print(\"Rows Number of ClosedStorePDF_1:\", ClosedStorePDF_1[\"日期\"].count())\n",
    "print(\"Rows Number of ClosedStorePDF_2:\", ClosedStorePDF_2[\"日期\"].count())\n",
    "ClosedStorePDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ea8b093d",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
